package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import util.ConexionBD;
import excepcion.DAOExcepcion;
import modelo.mCategoria;
import modelo.mPlanNegocio;
import modelo.mSabio;

public class daoPlanNegocio{
	public mPlanNegocio insertar(mPlanNegocio pn) throws DAOExcepcion {
        int resultado = 0;
		String query = "insert into plannegocio(idSabio, idCategoria, txTitulo, txVision, txRCBP, dtTiempoEjecucion, dbInversion, dbMargen) values (?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, pn.getIdSabio());
			stmt.setInt(2, pn.getIdCategoria());
			stmt.setString(3, pn.getTitulo());
			stmt.setString(4, pn.getVision());
			stmt.setString(5, pn.getRCBP());
			stmt.setString(6, pn.getTiempoEjecucion());
			stmt.setFloat(7, pn.getInversion());
			stmt.setFloat(8, pn.getMargen());
            int i = stmt.executeUpdate();
            if (i != 1) {
                throw new SQLException("No se pudo insertar");
            }
            // Obtener el ultimo id
            int id = 0;
            query = "select last_insert_id()";
            stmt = con.prepareStatement(query);
            rs = stmt.executeQuery();
            if (rs.next()) {
                id = rs.getInt(1);
            }
            pn.setIdPlanNegocio(id);
            
            query = "insert into estadoplan(idPlanNegocio, idEstado) values (?,1)";
            stmt = con.prepareStatement(query);
			stmt.setInt(1, id);
			int j = stmt.executeUpdate();
		} catch (SQLException e) {
            System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
		  /*this.cerrarResultSet(rs);
		  this.cerrarStatement(stmt);
		  this.cerrarConexion(con);*/
		}
		return pn;
	}
	
	public Collection<mPlanNegocio> listar(int idSabio) throws DAOExcepcion{
		Collection<mPlanNegocio> li = new ArrayList<mPlanNegocio>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "select idPlanNegocio, txTitulo, idCategoria,  dtTiempoEjecucion, dbInversion, 1 as estado from plannegocio where inEliminar = 0 and idSabio = ? order by idPlanNegocio";
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idSabio);
			rs = stmt.executeQuery();
			while (rs.next()) {
				mPlanNegocio mSa = new mPlanNegocio();
				mSa.setIdPlanNegocio(rs.getInt(1));
				mSa.setTitulo(rs.getString(2));
				mSa.setCategoria(rs.getString(3));
				mSa.setTiempoEjecucion(rs.getString(4));
				mSa.setInversion(rs.getFloat(5));
				mSa.setIdEstado(rs.getInt(6));
				li.add(mSa);
			}
		
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			
		}
		return li;
	}
	
	public Collection<mCategoria> lsCategoria() throws DAOExcepcion{
		Collection<mCategoria> li = new ArrayList<mCategoria>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "select idCategoria, txDescripcion from categoria where inEliminar = 0 order by txDescripcion";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				mCategoria mCa = new mCategoria();
				mCa.setIdCategoria(rs.getInt(1));
				mCa.setDescripcion(rs.getString(2));
				li.add(mCa);
			}
		
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			
		}
		return li;
	}
}